<?php
/**
 * @file
 *   Database query functions.
 * 
 * Ease of use functions for querying the database.
 * 
 * @todo Add functions related to the drupal site database (e.g. points).
 * @todo Add function to handle trading logic.
 */
require_once("config.php"); // Global constant configuration settings.

mysql_connect(DB_SERVER . ':' . DB_PORT, DB_USERNAME, DB_PASSWORD)
	or die(mysql_error());

mysql_select_db(DB_FACEBOOK);

/**
 * Queries database for a user's Untold ID.
 * 
 * @param $facebook_id
 *   A user's Facebook ID.
 * 
 * @return
 *   Resource containing a user's Untold ID on success, FALSE on failure.
 * 
 * @see execute_query($query)
 */
function query_select_untold_id($facebook_id) {
	return execute_query(
		"SELECT drupaluid
		FROM facebookUser
		WHERE facebookID='%s'",
		$facebook_id);
}

/**
 * Queries Untold's database for the user's Untold ID.
 * 
 * @param $username
 *   A user's Untold username.
 * @param $hash
 *   A user's Untold password.
 * 
 * @return
 *   Resource containing a user's Untold ID on success, FALSE on failure.
 * 
 * @see execute_query($query)
 */
function query_select_untold_id_with_credentials($username, $password) {
	return execute_query(
		"SELECT uid
		FROM untol1_testdru.druusers
		WHERE name = '%s' AND pass = '%s'",
		$username, $password);
}

/**
 * Queries database for the ID of a trade between two users.
 * 
 * @param $user_id_1
 *   Untold ID of the first user involved in the trade.
 * @param $user_id_2
 *   Untold ID of the second user involved in the trade.
 * 
 * @return
 *   Resource containing the trade ID on success, FALSE on failure.
 * 
 * @see execute_query($query)
 */
function query_select_trade_id($user_id_1, $user_id_2) {
	return execute_query(
		"SELECT tradeid
		FROM Trade
		WHERE (userid1='%s' AND userid2='%s')
		OR (userid1='%s' AND userid2='%s')",
		$user_id_1, $user_id_2, $user_id_2, $user_id_1);
}

/**
 * Queries database for the pending trade IDs for a given user.
 * 
 * @param $user_id
 *   Untold ID of a user.
 * 
 * @return
 *   Resource containing the trade IDs on success, FALSE on failure.
 * 
 * @see execute_query($query)
 */
function query_select_pending_trades($user_id) {
	return execute_query(
		"SELECT tradeid
		FROM Trade
		WHERE userid1='%s' OR userid2='%s'",
		$user_id, $user_id);
}

/**
 * Queries database for the Facebook and Untold IDs of friends who are
 * involved in pending trades with the given Facebook user.
 * 
 * @param $facebook_id
 *   Facebook ID of a user.
 * 
 * @return
 *   Resource containing Facebook and Drupal IDs of friends in pending trades
 *   with the user on succes, FALSE on failure.
 * 
 * @see execute_query($query)
 **/
function query_select_pending_trade_friend_ids($facebook_id) {
	return execute_query(
		"SELECT facebookID, drupaluid
		FROM facebookUser
		WHERE drupaluid IN
		(SELECT t.userid2#f.drupaluid
		FROM facebookUser f
		JOIN Trade t ON (f.drupaluid=t.userid1 AND f.drupaluid!=t.userid2)
		WHERE f.facebookID='%s'))",
		$facebook_id);
}

/**
 * Inserts into the facebookUser table an entry that associates the user's
 * Facebook and Untold IDs.
 * 
 * @param $facebook_id
 *   A user's Facebook ID.
 * @param $untold_id
 *   A user's Untold ID.
 * 
 * @return
 *   TRUE on success, FALSE on failure.
 * 
 * @see execute_query($query)
 */
function query_insert_user($facebook_id, $untold_id) {
	return execute_query(
		"INSERT
		INTO facebookUser (facebookID, drupaluid)
		VALUES ('%s', '%s')",
		$facebook_id, $untold_id);
}

/**
 * Deletes entry that contains the association between a user's Untold ID and
 * Facebook ID.
 * 
 * @param $facebook_id
 *   A user's Facebook ID.
 * 
 * @return
 *   TRUE on success, FALSE on failure.
 * 
 * @see execute_query($query)
 */
function query_delete_user($facebook_id) {
	return execute_query(
		"DELETE
		FROM facebookUser
		WHERE facebookID = '%s'",
		$facebook_id);
}

/**
 * Selects from the database a set of cards based on type, user's facebook id,
 * and/or trade id.
 * 
 * @param $type_filter
 *   Card type filter.
 * @param $fb_id
 *   A user's Facebook ID.
 * @param $trade_id
 *   Trade ID associated with the user.
 * 
 * @return
 *   Resource containing the cards based on type, user's facebook id, and/or
 *   trade id.
 * 
 * @see execute_query($query)
 */
function query_select_cards($type_filter = NULL, $facebook_id = NULL, $trade_id = NULL) {
	$query = "SELECT c.full_id, c.name, c.card_img";
	if($facebook_id)
		$query .= ", p.possessionid";
	
	$query .= " FROM cards_lu c";
	if($facebook_id)
		$query .= " JOIN Possession p ON c.full_id=p.cardid"
				. " JOIN facebookUser f ON f.drupaluid=p.userid";
	if($trade_id)
		$query .= " JOIN TradePossession t ON p.possessionid=t.possessionid";
	
	$query .= " WHERE c.front IS NULL";
	if($type_filter)
		$query .= " AND c.type=" . $type_filter;
	if($facebook_id)
		$query .= " AND f.facebookID=" . $facebook_id;
	if($trade_id)
		$query .= " AND t.tradeid=" . $trade_id;
	return execute_query($query);
}

/**
 * Executes a query after sanitizing arguement values.
 * 
 * @param $query
 *   Unsanitized arguement list consisting of a formatted query string and
 *   values.
 * 
 * @return
 *   Depends on the nature of the query:
 *   - SELECT, SHOW, DESCRIBE, EXPLAIN, etc: Resource on success, FALSE on
 *     failure.
 *   - INSERT, UPDATE, DELETE, DROP, etc: TRUE on success, FALSE on failure.
 * 
 * @see query_select_untold_id($fb_id)
 * @see query_select_untold_id_with_credentials($username, $hash)
 * @see query_select_trade_id($user_id_1, $user_id_2)
 * @see query_select_pending_trades($user_id)
 * @see query_select_pending_trade_friend_ids($facebook_id)
 * @see query_insert_user($facebook_id, $untold_id)
 * @see query_delete_user($fb_id)
 * @see query_select_cards($type_filter, $fb_id, $trade_id)
 * @see execute_query_unsafe($query)
 */
function execute_query($query) {
	$sanitized_args = array();

	// Sanitize arguements.
	$first_argument_index = 1;
	$argument_count = func_num_args() - $first_argument_index;
	for($i = 0; $i < $argument_count; ++$i) {
		$arg = func_get_arg($first_argument_index + $i);
		$sanitized_args[$i] = mysql_real_escape_string($arg);
	}

	$sanitized_query = vsprintf($query, $sanitized_args);

	// Now that the query has been sanitized, it can be executed
	return execute_query_unsafe($sanitized_query);
}

/**
 * Executes query without sanitizing arguement values.
 * 
 * @param $query
 *   Query string that may or may not be safe.
 * 
 * @return
 *   Depends on the nature of the query:
 *   - SELECT, SHOW, DESCRIBE, EXPLAIN, etc: Resource on success, FALSE on
 *     failure.
 *   - INSERT, UPDATE, DELETE, DROP, etc: TRUE on success, FALSE on failure.
 * 
 * @see execute_query($query)
 */
function execute_query_unsafe($query) {
	$result = mysql_query($query);
	if(!$result)
		die(mysql_error());
	return $result;
}

/**
 * Begin transaction.
 * 
 * Begins a new transaction. Must use commit() or rollback() to end
 * transaction.
 * 
 * @see commit()
 * @see rollback()
 */
function start_transaction() {
	execute_query_unsafe("START TRANSACTION");
}

/**
 * Commit transaction.
 * 
 * Ends and commints the current transaction.
 * 
 * @see start_transaction()
 */
function commit() {
	execute_query_unsafe("COMMIT");
}

/**
 * Rolls back transaction.
 * 
 * Ends and rolls back the current transaction.
 * 
 * @see start_transaction()
 */
function rollback() {
	execute_query_unsafe("ROLLBACK");
}
?>
